﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Medical_Store_Billing.Component;
using System.Windows.Forms;
using Medical_Store_Billing.Object_Class;

namespace Medical_Store_Billing.Data_Access_Layer
{
    class sanphamDAL
    {
        Dataservice m_sanpham = new Dataservice();
        public DataTable Timsp(string masp)
        {
            SqlCommand cmd = new SqlCommand("Select * from sanpham where mahang like N'%" + masp + "%'");
            m_sanpham.Load(cmd);
            return m_sanpham;
        }
        public DataTable Laydssanpham1()
        {
            SqlCommand cmd = new SqlCommand("Select mahang,tenhang,mancc,gia from sanpham order by mahang ASC");
            m_sanpham.Load(cmd);
            return m_sanpham;
        }
        public DataTable Laydssanpham()
        {
            SqlCommand cmd = new SqlCommand("Select * from sanpham order by mahang ASC");
            m_sanpham.Load(cmd);
            return m_sanpham;
        }
        public bool capnhatsoluongsp_tuphieunhap(int soluong,string mahang)
        {
            SqlCommand cmd = new SqlCommand("Update sanpham set soluongcon =soluongcon+@soluong where mahang = @mahang");
            cmd.Parameters.Add("soluong", SqlDbType.Int).Value = soluong;
            cmd.Parameters.Add("mahang", SqlDbType.VarChar).Value = mahang;
           
            if (m_sanpham.ExecuteNonQuery(cmd) > 0)
            {

                return true;
            }
            else return false;
        }
        public bool capnhatsoluongsp_tuphieuxuat(int soluong, string mahang)
        {
            SqlCommand cmd = new SqlCommand("Update sanpham set soluongcon =soluongcon-@soluong where mahang = @mahang");
            cmd.Parameters.Add("soluong", SqlDbType.Int).Value = soluong;
            cmd.Parameters.Add("mahang", SqlDbType.VarChar).Value = mahang;

            if (m_sanpham.ExecuteNonQuery(cmd) > 0)
            {

                return true;
            }
            else return false;
        }
        public DataTable Laydongia(String mahang)
        {
            SqlCommand cmd = new SqlCommand("Select gia from sanpham where mahang='"+mahang+"'");
            m_sanpham.Load(cmd);
            return m_sanpham;
        }
        public DataTable Demsanpham()
        {
            SqlCommand cmd = new SqlCommand("select count(*) from sanpham");
            m_sanpham.Load(cmd);

            return m_sanpham;
        }
        public bool Themsp(sanphamclass m_sp)
        {
            SqlCommand cmd = new SqlCommand("Insert into sanpham values(@mahang,@tenhang,@gia,@ghichu,@soluong,@mancc)");
            cmd.Parameters.Add("mahang", SqlDbType.VarChar).Value = m_sp.mahang;
            cmd.Parameters.Add("tenhang", SqlDbType.NVarChar).Value = m_sp.tenhang;
            cmd.Parameters.Add("gia", SqlDbType.Float).Value = m_sp.gia;
            cmd.Parameters.Add("ghichu", SqlDbType.Text).Value = m_sp.ghichu;
            cmd.Parameters.Add("soluong", SqlDbType.Int).Value = m_sp.soluongcon;
            cmd.Parameters.Add("mancc", SqlDbType.VarChar).Value = m_sp.mancc;
            if (m_sanpham.ExecuteNonQuery(cmd) > 0)
            {
                MessageBox.Show("Thêm sp Thanh Cong!");
                return true;
            }
            else return false;
        }

        public bool Suasp(sanphamclass m_sp)
        {
            SqlCommand cmd = new SqlCommand("Update sanpham set tenhang = @tenhang, gia = @gia, ghichu = @ghichu, soluongcon = @soluong, mancc = @mancc where mahang = @mahang");
            cmd.Parameters.Add("mahang", SqlDbType.VarChar).Value = m_sp.mahang;
            cmd.Parameters.Add("tenhang", SqlDbType.NVarChar).Value = m_sp.tenhang;
            cmd.Parameters.Add("gia", SqlDbType.Float).Value = m_sp.gia;
            cmd.Parameters.Add("ghichu", SqlDbType.Text).Value = m_sp.ghichu;
            cmd.Parameters.Add("soluong", SqlDbType.Int).Value = m_sp.soluongcon;
            cmd.Parameters.Add("mancc", SqlDbType.VarChar).Value = m_sp.mancc;

            if (m_sanpham.ExecuteNonQuery(cmd) > 0)
            {
                MessageBox.Show("Sửa SP thành công!");
                return true;
            }
            else return false;
        }
    }
}
